//Documentation complete
/**
 * @author Jason Funk
 * @version 2009FE22
 */

package broker;

import problemDomain.*;
import static problemDomain.ErrorBean.throwError;
import static problemDomain.ErrorBean.ErrorCode.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import static java.sql.Types.*;

/**
 * <code>Broker</code> for <code>MenuItemBeas</code>.
 */
public class MenuItemBroker extends Broker<MenuItemBean> {
	
	/* Attributes */
	
	private int newMenuItemID;
	
	/* END Attributes */
	
	
	/* Singleton Constructor */
	
	private static MenuItemBroker broker;
	
	/**
	 * Default constructor. Calls the openConnection method.
	 * @throws ErrorBean
	 */
	private MenuItemBroker () throws ErrorBean {
		insertSQL = 
		deleteMenuItemSQL = null;
		insertMenuItemListSQL =
		updateSQL = 
		addFoodItemSQL = 
		deleteFoodItemSQL = null;
	}
	
	/**
	 * Retrieves the static MenuItemBroker instance.
	 * @return static MenuItemBroker
	 * @throws ErrorBean
	 */
	public synchronized static MenuItemBroker getBroker () throws ErrorBean {
		if (broker == null)
			broker = new MenuItemBroker();
		return broker;
	}
	
	/* END Singleton Constructor */
	
	
	/* Prepared Statements */
	
	private CallableStatement insertSQL;
	private CallableStatement getInsertSQL () throws ErrorBean {
		CallableStatement statement = prepare(insertSQL, "CALL sp_create_menu_item(?, ?, ?, ?, ?);");
		return registerOutParameter(statement, 5, SMALLINT);
	}
	
	private PreparedStatement insertMenuItemListSQL;
	private PreparedStatement getInsertMenuItemListSQL () throws ErrorBean {
		return prepare(insertMenuItemListSQL, "INSERT INTO menu_item_list SET fk_menu_item_id = ?, fk_food_item_id = ?;");
	}
	
	private PreparedStatement updateSQL;
	private PreparedStatement getUpdateSQL () throws ErrorBean {
		return prepare(updateSQL, "UPDATE menu_item SET fk_menu_id = ?, number = ?, name = ?, price = ? WHERE menu_item_id = ?;");
	}
	
	private CallableStatement deleteMenuItemSQL;
	private CallableStatement getDeleteMenuItemSQL () throws ErrorBean {
		return prepare(deleteMenuItemSQL, "CALL sp_delete_menu_item(?);");
	}
	
	private PreparedStatement addFoodItemSQL;
	private PreparedStatement getAddFoodItemSQL () throws ErrorBean {
		return prepare(addFoodItemSQL, "INSERT INTO menu_item_list SET fk_menu_item_id = ?, fk_food_item_id = ?;");
	}
	
	private PreparedStatement deleteFoodItemSQL;
	private PreparedStatement getDeleteFoodItemSQL () throws ErrorBean {
		return prepare(deleteFoodItemSQL, "DELETE FROM menu_item_list WHERE fk_menu_item_id = ? AND fk_food_item_id = ?;");
	}
	
	/* END Prepared Statements */
	
	
	/* (Broker) Methods */
	
	@Override
	public synchronized boolean persist (MenuItemBean mib) throws ErrorBean {		
		mib.isValid();
		
		int id = mib.getId();
		int menu_id = 0;
			if (mib.getMenu() != null)
				menu_id = mib.getMenu().getId();
		String name = mib.getName();
		double price = mib.getPrice();
		int number = mib.getNumber();
		List<FoodItemBean> foodItems = mib.getItemList();
		
		if (id == 0) {
			try {
				CallableStatement insertSQL = getInsertSQL();
				if (menu_id > 0)
					insertSQL.setInt(1, menu_id);
				else
					insertSQL.setNull(1, INTEGER);
				insertSQL.setInt(2, number);
				insertSQL.setString(3, name);
				insertSQL.setDouble(4, price);
				insertSQL.executeUpdate();
				
				int new_menu_item_id = insertSQL.getInt(5);
				newMenuItemID = new_menu_item_id;
				// Add food items to the menu item list
				if (foodItems != null)
					for (int i = 0; i < foodItems.size(); i++) {
						PreparedStatement insertMenuItemListSQL = getInsertMenuItemListSQL();
						insertMenuItemListSQL.setInt(1, new_menu_item_id);
						insertMenuItemListSQL.setInt(2, menu_id);
						insertMenuItemListSQL.execute();
					}
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_INSERT_ERROR,
					e.getMessage(),
					this);
			}
		} else if (id > 0) {
			try {
				PreparedStatement updateSQL = getUpdateSQL();
				if (menu_id > 0)
					updateSQL.setInt(1, menu_id);
				else
					updateSQL.setNull(1, Types.INTEGER);
				updateSQL.setInt(2, number);
				updateSQL.setString(3, name);
				updateSQL.setDouble(4, price);
				updateSQL.setInt(5, id);
				updateSQL.execute();
				
				updateSQL.execute();
				return true;
			} catch(SQLException e) {
				throwError(
					BROKER_UPDATE_ERROR,
					e.getMessage(),
					this);
			}
		}
		return false;
	}

	@Override
	public synchronized boolean remove(MenuItemBean mib) throws ErrorBean	{
		int menu_item_id = mib.getId();
		
		try {
			CallableStatement deleteMenuItemSQL = getDeleteMenuItemSQL();
			deleteMenuItemSQL.setInt(1, menu_item_id);
			deleteMenuItemSQL.executeUpdate();
			return true;
		} catch(SQLException e) {
			throwError(
				BROKER_DELETE_ERROR,
				e.getMessage(),
				this);
			return false;
		}
	}
	
	@Override
	public synchronized List<MenuItemBean> search (MenuItemBean searchBean) throws ErrorBean {
		List<MenuItemBean> search = new ArrayList<MenuItemBean>();
		MenuItemBean mib;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM menu_item WHERE active = TRUE");
		
		//Search by Menu Item ID
		if (searchBean.getId() > 0) {
			int menu_item_id = searchBean.getId();
			sql.append(" AND menu_item_id = " + menu_item_id);
		} else {
			//Search by Menu Id
			if (searchBean.getMenu() != null) {
				int menu_id = searchBean.getMenu().getId();
				if (menu_id > 0)
					sql.append(" AND fk_menu_id = " + menu_id);
			}
			
			//Search by Menu Item number
			int number = searchBean.getNumber();
			if (number > 0) {
				sql.append(" AND number = " + number);
			}
			
			//Search by Menu Item price
			double price = searchBean.getPrice();
			if (price > 0) {
				sql.append(" AND price = " + price);
			}
			
			//Search by Menu Item name
			String name = searchBean.getName();
			if (name != null && !"".equals(name)) {
				sql.append(" AND UPPER(name) LIKE UPPER('%" + name + "%')");
			}
		}
		sql.append(';');
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					mib = new MenuItemBean();
					int menu_item_id = results.getInt("menu_item_id");
					mib.setId(menu_item_id);
					
					//Search for menu name
					MenuBroker mb = MenuBroker.getBroker();
					int menuId = results.getInt("fk_menu_id");
					MenuBean menu = null;
					if (menuId > 0) {
						menu = new MenuBean(menuId);
						menu.setName(mb.getMenuName(new MenuBean(menuId)));
					}
					mib.setMenu(menu);
					mib.setName(results.getString("name"));
					mib.setPrice(results.getDouble("price"));
					mib.setNumber(results.getInt("number"));
					
					//set menu item list
					mib.setItemList(getMenuItemList(menu_item_id));
					search.add(mib);
				}
			}			
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	@Override
	public synchronized List<MenuItemBean> listAll() throws ErrorBean {
		List<MenuItemBean> search = new ArrayList<MenuItemBean>();
		MenuItemBean mib;
		
		StringBuilder sql = new StringBuilder("SELECT * FROM menu_item WHERE active = TRUE ORDER BY number;");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					mib = new MenuItemBean();
					mib.setId(results.getInt("menu_item_id"));
					// search for the menu and set it
					MenuBroker mb = MenuBroker.getBroker();
					int menuId = results.getInt("fk_menu_id");
					MenuBean menu = null;
					// set the menu with only menuId and name
					// note that search method of menuBroker cannot be used!!!
					if (menuId > 0) {
						menu = new MenuBean(menuId);
						menu.setName(mb.getMenuName(new MenuBean(menuId)));
					}
					mib.setMenu(menu);
					mib.setName(results.getString("name"));
					mib.setPrice(results.getDouble("price"));
					mib.setNumber(results.getInt("number"));
					
					search.add(mib);
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return search;
	}
	
	/* END (Broker) Methods */
	
	
	/* Accessors */
	
	/**
	 * Returns the new menu item ID.
	 * @return the new menu item ID
	 */
	public int getNewMenuItemID () {
		return newMenuItemID;
	}
	
	/* END Accessors */
	
	
	/* Methods */
	
	/**
	 * Adds a food item to a menu in the database.
	 * @param menuItem the menu
	 * @param foodItem the food item
	 * @throws ErrorBean if there is an error persisting the data 
	 */
	public void addFoodItem (MenuItemBean menuItem, FoodItemBean foodItem) throws ErrorBean {
		try {
			PreparedStatement stmt = getAddFoodItemSQL();
			stmt.setInt(1, menuItem.getId());
			stmt.setInt(2, foodItem.getId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throwError(
				BROKER_INSERT_ERROR,
				e.getMessage(),
				this);
		}
	}
	
	/**
	 * Removes a food item from a menu in the database.
	 * @param menuItem the menu
	 * @param foodItem the food item
	 * @throws ErrorBean if there is an error persisting the data 
	 */
	public void removeFoodItem (MenuItemBean menuItem, FoodItemBean foodItem) throws ErrorBean {
		try	{
			PreparedStatement stmt = getDeleteFoodItemSQL();
			stmt.setInt(1, menuItem.getId());
			stmt.setInt(2, foodItem.getId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throwError(
					BROKER_DELETE_ERROR,
					e.getMessage(),
					this);
		}
	}
	
	/* END Methods */
	
	
	/* Local Methods */
	
	private List<FoodItemBean> getMenuItemList(int menu_item_id) throws ErrorBean {
		List<FoodItemBean> menuItemList = new ArrayList<FoodItemBean>();
		FoodItemBean searchFoodItemBean;
		FoodItemBroker fib;
		
		StringBuffer sql = new StringBuffer("SELECT fk_food_item_id FROM menu_item_list WHERE fk_menu_item_id = "+ menu_item_id +";");
		
		try	{
			Statement stmt = conn.createStatement();
			if (!sql.toString().equals("")) {
				ResultSet results = stmt.executeQuery(sql.toString());
				
				while (results.next()) {
					fib = FoodItemBroker.getBroker();
					searchFoodItemBean = new FoodItemBean();
					searchFoodItemBean.setId(results.getInt("fk_food_item_id"));
					
					menuItemList.addAll(fib.search(searchFoodItemBean));
				}
			}
		} catch (SQLException e) {
			throwError(
				BROKER_SELECT_ERROR,
				e.getMessage(),
				this);
		}
		return menuItemList;
	}
	
	/* END Local Methods */
	
}
